/**
 * 
 */
package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.BookDao;
import bookshop.dao.CustomerDao;
import bookshop.dao.DaoFactory;
import bookshop.dao.TempStoreDao;

import bookshop.domain.Customer;
import bookshop.domain.TempStore;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.CategoryQueryHelper;
import bookshop.service.helper.TempStoreQueryHelper;
import bookshop.utils.DBUtils;

/**
 * @author Administrator
 *
 */
public class TempStoreDaoJDBCImpl implements TempStoreDao {

	private static final String ADD_BOOKTOSTORE ="insert into temp_store(customer_id,book_code,store_time) values(?,?,?)";
	private static final String GET_BOOKBYID ="select * from temp_store where customer_id=?";
	private static final String DEL ="delete from temp_store where id=?";
	
	

	private String generateSQL(TempStoreQueryHelper helper){
		
		String sql="select * from temp_store where 1=1";
		
		
		if(helper.getQryBookCode()!=null)
			sql+=" and book_code like '%"+helper.getQryBookCode()+"%' ";
		
		if(helper.getQryBookName()!=null)
			sql += " and book_name like '%" +helper.getQryBookName() +"%' ";
			
		if(helper.getQryStoreTime()!=null){
			if(helper.getQryStoreTime().equals("up"))
				sql += " order by store_time asc";
			if(helper.getQryStoreTime().equals("down"))
				sql += " order by store_time desc";
		}
			
		if(helper.getBookPriceSequence()!=null){
			if(helper.getBookPriceSequence().equals("up")){
				sql += " order by book_price asc";}
			
			if(helper.getBookPriceSequence().equals("down")){
				sql += " order by book_price desc";}
		}
		
		if(helper.getQryStoreTime()==null && helper.getBookPriceSequence()==null)
			sql+=" order by id";
		
		return sql;
		
	}
	
	public void addBookToStore(TempStore ts) {
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		
		
		try {
			ps=ct.prepareStatement(ADD_BOOKTOSTORE);
			ps.setString(1, ts.getCustomer().getCustomerId());
			ps.setString(2, ts.getBook().getBookCode());
			ps.setTimestamp(3, new java.sql.Timestamp(ts.getStoreTime().getTime()));
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
	}




	
	public List<TempStore> getBookByCustID(String customerId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<TempStore> tss = new ArrayList<TempStore>();
		TempStore ts=null;
		
		try {
			ps=ct.prepareStatement(GET_BOOKBYID);
			ps.setString(1, customerId);
			rs=ps.executeQuery();
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory.getDao("customerDao");
			while(rs.next()){
				ts=new TempStore();
				ts.setTempId(rs.getInt(1));
				ts.setCustomer(customerDao.getCustomerById(rs.getString(2)));
				ts.setBook(bookDao.getBookByCode(rs.getString(3)));
				ts.setStoreTime(new java.util.Date(rs.getTimestamp(4).getTime()));
				tss.add(ts);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);

		}
		return tss;
		
	}





	
	public void deleteBook(Integer Id) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		
		try {
			ps=ct.prepareStatement(DEL);
			ps.setInt(1, Id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
		
	}





	


	public List<TempStore> getTempStoreByCondition(TempStoreQueryHelper helper) {
		
		String sql = this.generateSQL(helper);
		
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<TempStore> tss = new ArrayList<TempStore>();
		TempStore ts=null;
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory.getDao("customerDao");
			while(rs.next()){
				
				ts=new TempStore();
				ts.setTempId(rs.getInt(1));
				ts.setCustomer(customerDao.getCustomerById(rs.getString(2)));
				ts.setBook(bookDao.getBookByCode(rs.getString(3)));
				ts.setStoreTime(new java.util.Date(rs.getTimestamp(4).getTime()));
			    tss.add(ts);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("没有找到符合条件的图书!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return tss;
	}

	
	public Long countTempStoreByConditon(TempStoreQueryHelper helper) {
		
		String sql = this.generateSQL(helper);
		

		sql = sql.replace("*", "count(*) as total");
		sql = sql.substring(0, sql.indexOf("order by"));
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return count;
	}

	
	public List<TempStore> getScopedTempStores(TempStoreQueryHelper helper,
			int startIndex, int fetchSize) {
		
		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;
		
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TempStore> tss = new ArrayList<TempStore>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			BookDao bookDao = (BookDao) DaoFactory.getDao("bookDao");
			CustomerDao customerDao = (CustomerDao) DaoFactory.getDao("customerDao");
			while(rs.next()){
				TempStore ts=new TempStore();
				ts=new TempStore();
				ts.setTempId(rs.getInt(1));
				ts.setCustomer(customerDao.getCustomerById(rs.getString(2)));
				ts.setBook(bookDao.getBookByCode(rs.getString(3)));
				
				ts.setStoreTime(new java.util.Date(rs.getTimestamp(4).getTime()));
			    tss.add(ts);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("没有找到符合条件的图书!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		
		return tss;
	}




}
